/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bookingsystem;


import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.Socket;
import java.sql.*;
import java.util.ArrayList;
import javax.swing.JOptionPane;



/**
 *
 * @author admin
 */
public class ClientThread implements Runnable {
    private Socket client = null;
    ObjectOutputStream oos = null;
    ObjectInputStream ois = null;
    ResultSet rset, rowCount = null;
    ArrayList<Reservation> reservationsArray;
    ArrayList<Flight> flightsArray;
    ArrayList<Reservation> reservationsUpdates;
    
    public ClientThread(Socket client) {
        this.client = client;
        try {
            oos = new ObjectOutputStream(client.getOutputStream());
            ois = new ObjectInputStream(client.getInputStream());
        } catch (IOException e){
            System.out.println(e);
        }
    }
    
    public void run() {
        try {
            String[] options = {"Update","Upload"};
            int option = 0;
            option = JOptionPane.showOptionDialog(null, "Update or Upload?", "Select option", 2, 3, null, options, 0);
            ToCompanyServer transmitWindow = new ToCompanyServer();
            transmitWindow.setTitle("Transmitting data");
            transmitWindow.setVisible(true);
            Object ready = null;
            oos.writeObject("readyToReceive");
            oos.flush();
            ready = ois.readObject();
            if (ready.equals("ready")) {
                if (option == 1) {//upload new reservations
                    sendReservations();
                    receiveReservations();
                }
                else//update reservation from other helicopters and city
                    updateLists();
            }
        }
        catch (ClassNotFoundException e){
            System.out.println(e);
        }
        catch (IOException e){
            System.out.println(e);
        }
    }
    
    public void sendReservations() {
        try {
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s = connection.createStatement();
            ArrayList reservations = new ArrayList(); 
            rowCount = s.executeQuery("Select count(idreservation) from reservation where status='Awaiting' or status='OK' or status='Cancelled'");
            while (rowCount.next()){
                int numRows = (int) Integer.parseInt(rowCount.getString(1));
            }
            rset = s.executeQuery("Select * from reservation where status='Awaiting' or status='OK' or status='Cancelled' order by idreservation desc");
            //Reservation[] reservations = new Reservation[numRows];
            while (rset.next()){
                String reservationId = rset.getString(1);
                String origin = rset.getString(2);
                String destination = rset.getString(3);
                String flightDate = rset.getString(4);
                String date = flightDate.substring(0, 10);
                String time = flightDate.substring(11, 16);
                int flightNo = rset.getInt(5);
                String bookingDate = rset.getString(6);
                String custName = rset.getString(7);
                String custLastName = rset.getString(8);
                String status = rset.getString(9);
                reservations.add(new Reservation(reservationId,flightNo,custName,custLastName,origin,destination,date,time,bookingDate,status));
            }
//            reservation = (Reservation) ois.readObject();

            oos.writeObject("reservationsList");
            oos.flush();
            oos.writeObject(reservations);
            oos.flush();
            
        }
        catch (IOException e){
            System.out.println(e);
        }
        catch (SQLException e) { System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
    
    public void receiveReservations() {
        try {
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s = connection.createStatement();
            
            reservationsArray = (ArrayList) ois.readObject();
            
            String reservationId = "";
            String origin = "";
            String destination = "";
            String flightDate = "";
            String flightTime = "";
            String bookingDate = "";
            String custName = "";
            String custLastName = "";
            String status = "";
            String reservationIdLetters = "";
            int flightNo = 0;
            int i = 0;
            while (i<reservationsArray.size()){
                reservationId = reservationsArray.get(i).getReservationId();
                origin = reservationsArray.get(i).getOrigin();
                destination = reservationsArray.get(i).getDestination();
                flightDate = reservationsArray.get(i).getDate();
                flightTime = reservationsArray.get(i).getTime();
                flightDate = flightDate+" "+flightTime;
                bookingDate = reservationsArray.get(i).getBookingDate();
                flightNo = reservationsArray.get(i).getFlightNo();
                custName = reservationsArray.get(i).getName();
                custLastName = reservationsArray.get(i).getLastName();
                status = reservationsArray.get(i).getStatus();
                reservationIdLetters = reservationId.substring(0, 2);
                if (!reservationIdLetters.equals("AA")) {
                    s.execute("Insert into reservation values ('"+reservationId+"','"+origin+"','"+destination+"','"+flightDate+"','"+flightNo+"','"+bookingDate+"','"+custName+"','"+custLastName+"','"+status+"')");
                }
                else
                    s.execute("Update reservation Set status='"+status+"' Where idreservation='"+reservationId+"'");
                //s.execute("Update flight Set booked_seats=booked_seats+1 Where idflight='"+flightNo+"'");
                i++;
            }
            //reservations for updates (confirmed reserved reservations or cancelled reserved reservations)
            reservationsUpdates = (ArrayList) ois.readObject();
            i = 0;
            if (reservationsUpdates != null){
                while (i<reservationsUpdates.size()){
                    reservationId = reservationsUpdates.get(i).getReservationId();
                    status = reservationsUpdates.get(i).getStatus();
                    s.execute("Update reservation Set status='"+status+"' Where idreservation='"+reservationId+"'");
                    i++;
                }
            }
        }
        catch (IOException e) {
            System.out.println(e);
        }
        catch (ClassNotFoundException e) {
            System.out.println(e);
        }
        catch (SQLException e) { 
            System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
        
    public void updateLists() {
        try {
            oos.writeObject("updateReservationsList");
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s = connection.createStatement();
            
            rset = s.executeQuery("Select book_date from reservation order by book_date desc limit 1");
            String lastBookingDate = "";
            while (rset.next()){
                lastBookingDate = rset.getString(1);
            }
            oos.writeObject(lastBookingDate);
            oos.flush();
            
            reservationsArray = (ArrayList) ois.readObject();

            String reservationId = "";
            String origin = "";
            String destination = "";
            String flightDate = "";
            String flightTime = "";
            String bookingDate = "";
            String custName = "";
            String custLastName = "";
            String status = "";
            String reservationIdLetters = "";
            int flightNo = 0;
            int i = 0;
            while (i<reservationsArray.size()){
                reservationId = reservationsArray.get(i).getReservationId();
                origin = reservationsArray.get(i).getOrigin();
                destination = reservationsArray.get(i).getDestination();
                flightDate = reservationsArray.get(i).getDate();
                flightTime = reservationsArray.get(i).getTime();
                flightDate = flightDate+" "+flightTime;
                bookingDate = reservationsArray.get(i).getBookingDate();
                flightNo = reservationsArray.get(i).getFlightNo();
                custName = reservationsArray.get(i).getName();
                custLastName = reservationsArray.get(i).getLastName();
                status = reservationsArray.get(i).getStatus();
                reservationIdLetters = reservationId.substring(0, 2);
                s.execute("Insert into reservation values ('"+reservationId+"','"+origin+"','"+destination+"','"+flightDate+"','"+flightNo+"','"+bookingDate+"','"+custName+"','"+custLastName+"','"+status+"')");
                s.execute("Update flight Set booked_seats=booked_seats+1 Where idflight='"+flightNo+"'");
                i++;
            }
            
            rset = s.executeQuery("Select idflight from flight order by idflight desc limit 1");
            String lastFlightNo = "";
            while (rset.next()){
                lastFlightNo = rset.getString(1);
            }
            oos.writeObject(lastFlightNo);
            oos.flush();
            
            flightsArray = (ArrayList) ois.readObject();

            int flightId = 0;
            origin = "";
            destination = "";
            flightDate = "";
            flightTime = "";
            int seatCapacity = 0;
            int bookedSeats = 0;
            i = 0;
            while (i<flightsArray.size()){
                flightId = flightsArray.get(i).getFlightNo();
                origin = flightsArray.get(i).getOrigin();
                destination = flightsArray.get(i).getDestination();
                flightDate = flightsArray.get(i).getDate();
                flightTime = flightsArray.get(i).getTime();
                flightDate = flightDate+" "+flightTime;
                seatCapacity = flightsArray.get(i).getSeatsCapacity();
                bookedSeats = flightsArray.get(i).getBookedSeats();
                s.execute("Insert into flight values ('"+flightId+"','"+seatCapacity+"','"+bookedSeats+"','"+origin+"','"+destination+"','"+flightDate+"')");
                i++;
            }
        }
        catch (IOException e) {
        System.out.println(e);
        }
        catch (ClassNotFoundException e) {
            System.out.println(e);
        }
        catch (SQLException e) { 
            System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
}
